<?php

class sfPropelRevisitedExporterXls extends sfPropelRevisitedExporter
{
  const
    FILE_EXTENSION    = 'xls',
    MIME_TYPE         = 'application/x-excel',
    COMMON_TITLE_ROW_NUMBER = 1,
    COMMON_DATE_ROW_NUMBER  = 2,
    TITLE_ROW_NUMBER  = 3,
    HEADER_ROW_NUMBER = 4;

  protected
    $numberOfRows     = 1,
    $rowInformation   = array(),
    $myContext        = null,
    $excelWriter      = null,
    $excelObject      = null;
  
  public function getNumberOfRows()
  {
    return $this->numberOfRows;
  }

  public function getTitleRowNumber()
  {
  }

  public function getHeaderRowNumber()
  {
  }

  public function getCommonTitleRowNumber()
  {
  }

  public function getCommonDateRowNumber()
  {
  }

  public function setRowInformation($rows)
  {
    $this->rowInformation = $rows;
  }

  static public function getFileExtension()
  {
    return self::FILE_EXTENSION;
  }

  static public function getMimeType()
  {
    return self::MIME_TYPE;
  }

  public function getRowInformation()
  {
    return $this->rowInformation;
  }

  public function build()
  {
    ini_set("max_execution_time",0); 

    $this->buildExcelObject();

    if ($this->getCommonTitle() !== false)
    {
      $this->buildCommonTitle();
    }
    if ($this->getCommonDate() != false)
    {
      $this->buildCommonDate();
    }
    $this->buildTitle();
    $this->buildHeaders($this->getHeaders());
    $this->buildRows($this->getRowInformation());
    $this->setAutosizeColumnDimensions();
    $column = chr(ord('A') + $this->getHeaderCount()-1);
    $this->excelObject->getActiveSheet()->getPageSetup()->setPrintArea("A1:$column".$this->getNumberOfRows());
  }

  public function setAutosizeColumnDimensions()
  {
    $this->excelObject->getActiveSheet()->getRowDimension(self::TITLE_ROW_NUMBER)->setRowHeight(20);
    $this->excelObject->getActiveSheet()->getRowDimension(self::COMMON_TITLE_ROW_NUMBER)->setRowHeight(25);
    for ($i=0;$i<$this->getHeaderCount();$i++)
    {
      $column = chr(ord('A') + $i);
      $this->excelObject->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
    }
  }

  public function saveFile($whereTo)
  {
    ini_set("max_execution_time",0); 
    $this->excelWriter = new PHPExcel_Writer_Excel5($this->getExcelObject());
    $this->excelWriter->save($whereTo);
  }

  protected function applyDefaultSheetStyle($sheet)
  {
    $sheet->getDefaultStyle()->getFont()->setSize(sfPropelRevisitedGeneratorConfiguration::getExportationFontSize());
    $sheet->getDefaultStyle()->getFont()->setName(sfPropelRevisitedGeneratorConfiguration::getExportationFontName());
    $sheet->getPageSetup()->setPaperSize(sfPropelRevisitedGeneratorConfiguration::getExportationPaperSize());
    $sheet->getPageSetup()->setOrientation(sfPropelRevisitedGeneratorConfiguration::getExportationOrientation());
    $sheet->getPageSetup()->setFitToWidth(sfPropelRevisitedGeneratorConfiguration::getExportationFitToPage());
    $sheet->getPageSetup()->setFitToHeight(false);
    $sheet->getPageSetup()->setHorizontalCentered(true);
    $sheet->getPageMargins()->setTop(sfPropelRevisitedGeneratorConfiguration::getExportationTopMargin());
    $sheet->getPageMargins()->setRight(sfPropelRevisitedGeneratorConfiguration::getExportationRightMargin());
    $sheet->getPageMargins()->setBottom(sfPropelRevisitedGeneratorConfiguration::getExportationBottomMargin());
    $sheet->getPageMargins()->setLeft(sfPropelRevisitedGeneratorConfiguration::getExportationLeftMargin());

    return $sheet;
  }

  protected function buildExcelObject()
  {
    $this->excelObject = new sfPhpExcel();
    $this->excelObject->setActiveSheetIndex(0);
    $this->applyDefaultSheetStyle($this->excelObject->getActiveSheet());
  }

  public function setExcelObject($excelObject)
  {
    $this->excelObject = $object;
  }

  public function getExcelObject()
  {
    return $this->excelObject;
  }

  protected function buildTitle()
  {
    $this->excelObject->getActiveSheet()->setCellValueByColumnAndRow(0, self::TITLE_ROW_NUMBER, $this->translate($this->getTitle()));
    $this->excelObject->getActiveSheet()->mergeCellsByColumnAndRow(0, self::TITLE_ROW_NUMBER, $this->getHeaderCount()-1, self::TITLE_ROW_NUMBER);
    $this->applyMergedCellStyle(self::TITLE_ROW_NUMBER, 1, $this->getHeaderCount()-1, $this->buildTitleFormat());
    $this->numberOfRows++;
  }

  protected function buildCommonTitle()
  {
    $this->excelObject->getActiveSheet()->setCellValueByColumnAndRow(0, self::COMMON_TITLE_ROW_NUMBER, $this->translate($this->getCommonTitle()));
    $this->excelObject->getActiveSheet()->mergeCellsByColumnAndRow(0, self::COMMON_TITLE_ROW_NUMBER, $this->getHeaderCount()-1, self::COMMON_TITLE_ROW_NUMBER);
    $this->applyMergedCellStyle(self::COMMON_TITLE_ROW_NUMBER, 1, $this->getHeaderCount()-1, $this->buildCommonTitleFormat());
    $this->numberOfRows++;
  }

  public function buildPageContextFormat()
  {
    $ret = array(
      'font'       => array('bold' => false,),
      'alignment'  => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'wrap'       => true,
                            ),
    );
    return $ret;
  }

  public function buildSubtitleFormat()
  {
    $ret = array(
      'font'       => array('bold' => true,),
      'alignment'  => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'wrap'       => true,
      ),
      'borders' => array(
        'top'     => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'bottom'  => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'left'    => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'right'   => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
      ),
    );
    return $ret;
  }

  public function applyMergedCellStyle($rowNumber, $columnStartCount, $columnEndCount, $style)
  {
    $columnL = chr(ord('A') + $columnStartCount-1);
    $columnR = chr(ord('A') + $columnEndCount-1);

    $this->excelObject->getActiveSheet()->getStyleByColumnAndRow($columnL.$rowNumber.":".$columnR.$rowNumber, $rowNumber)->applyFromArray($style);
  }

  public function buildTitleFormat()
  {
    $ret = array(
      'font'      => array(
        'bold' => true
      ),
      'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
      ),
      'borders' => array(
        'top'     => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'bottom'  => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'left'    => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'right'   => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
      ),
    );
    return $ret;
  }

  protected function buildGeneralFormat()
  {
    $ret = array(
      'borders' => array(
        'top'     => array('style' => PHPExcel_Style_Border::BORDER_THIN),
        'bottom'  => array('style' => PHPExcel_Style_Border::BORDER_THIN),
        'left'    => array('style' => PHPExcel_Style_Border::BORDER_THIN),
        'right'   => array('style' => PHPExcel_Style_Border::BORDER_THIN),
      ),
      'alignment'  => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT),
    );
    return $ret;
  }

  protected function buildHeaderFormat()
  {
    $ret = array(
      'font'      => array('bold'       => true),
      'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER),
      'borders' => array(
        'top'     => array('style' => PHPExcel_Style_Border::BORDER_THIN),
        'bottom'  => array('style' => PHPExcel_Style_Border::BORDER_THIN),
        'left'    => array('style' => PHPExcel_Style_Border::BORDER_THIN),
        'right'   => array('style' => PHPExcel_Style_Border::BORDER_THIN),
      ),
    );

    return $ret;
  }

  public function buildCommonTitleFormat()
  {
    $ret = array(
      'font'      => array('bold' => true, 'size' => 14),
      'alignment'  => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'wrap'       => true),
      'borders' => array(
        'top'     => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'bottom'  => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'left'    => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'right'   => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
      ),
    );
    return $ret;
  }

  protected function buildCommonDateFormat()
  {
    $ret = array(
      'font'      => array('bold' => false, 'size' => 10),
      'alignment'  => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'wrap'       => true),
      'borders' => array(
        'top'     => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'bottom'  => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'left'    => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
        'right'   => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => 'ffffff')),
      ),
    );
    return $ret;
  }

  protected function buildCommonDate()
  {
    $this->excelObject->getActiveSheet()->setCellValueByColumnAndRow(0, self::COMMON_DATE_ROW_NUMBER, $this->translate($this->getCommonDate()));
    $this->excelObject->getActiveSheet()->mergeCellsByColumnAndRow(0, self::COMMON_DATE_ROW_NUMBER, $this->getHeaderCount()-1, self::COMMON_DATE_ROW_NUMBER);
    $this->applyMergedCellStyle(self::COMMON_DATE_ROW_NUMBER, 1, $this->getHeaderCount()-1, $this->buildCommonDateFormat());
    $this->numberOfRows++;
  }

  protected function buildHeaders($headers)
  {
    $column = 0;
    foreach ($this->getHeaders() as $field)
    {
      $this->excelObject->getActiveSheet()->setCellValueByColumnAndRow($column, self::HEADER_ROW_NUMBER, $this->translate($field));
      $this->excelObject->getActiveSheet()->getStyleByColumnAndRow($column, self::HEADER_ROW_NUMBER)->applyFromArray($this->buildHeaderFormat());
      $column++;
    }
    $this->numberOfRows++;
  }

  protected function buildRows($rows)
  {
    $rowNumber = self::HEADER_ROW_NUMBER + 1;

    foreach ($rows as $line)
    {
      $this->buildRow($line, $rowNumber);
      $rowNumber++;
    }
  }

  protected function buildRow($row, $rowNumber)
  {
    $column = 0;
    foreach ($row as $key => $field)
    {
      $this->excelObject->getActiveSheet()->setCellValueByColumnAndRow($column, $rowNumber, $this->chopText($field));
      $this->excelObject->getActiveSheet()->getStyleByColumnAndRow($column, $rowNumber)->applyFromArray($this->buildGeneralFormat());
      $column++;
    }
    $this->numberOfRows++;
  }

  protected function chopText($text)
  {
    if (is_string($text) && !is_numeric($text))
    {
      if (strlen($text) > 50)
      {
        return substr($text, 0, 47).'...';
      }
    }
    return $text;
  }
}
